#!/usr/bin/env python
# -*- encoding: utf-8 -*-
import sys, datetime, json, logging, os
import pandas as pd
import numpy as np
#依赖需要自己安装，xpy3lib文件从硅钢算法服务器上，cd /apps/P_PROJECT/c512_cpc/;中查找
# from apscheduler.schedulers.blocking import BlockingScheduler
import math
from xpy3lib.utils.XDataFrameUtils import XDataFrameUtils
from xpy3lib.XRetryableQuery import XRetryableQuery
from xpy3lib.XRetryableSave import XRetryableSave

from xpy3lib.utils import db_utils as util
from xpy3lib.XLogger import XLogger
from sicost.config import app_config

"""

-------------------------------->> NOTE <<------------------------------------------------ 
Usage:
hold_rate.py
------------------------------------------------------------------------------------------


"""


def main():



    profile = 'dev'
    config = app_config[profile]

    try:
        # db_conn_sts = util.getConnectionSTS(config.DB_HOST_STS,
        #                                      config.DB_PORT_STS,
        #                                      config.DB_DBNAME_STS,
        #                                      config.DB_USER_STS,
        #                                      config.DB_PASSWORD_STS)
        # XLogger.getInstance().logger.info('connect db_conn_sts success')
        db_conn_mpp = util.getConnectionDb2(config.DB_HOST_MPP2_DB2,
                                            config.DB_PORT_MPP2_DB2,
                                            config.DB_DBNAME_MPP2_DB2,
                                            config.DB_USER_MPP2_DB2,
                                            config.DB_PASSWORD_MPP2_DB2)
        XLogger.getInstance().logger.info('connect db_conn_mpp success')
        # db_conn_mpp3 = util.getConnectionDb2(config.DB_HOST_MPP3_DB2,
        #                                     config.DB_PORT_MPP3_DB2,
        #                                     config.DB_DBNAME_MPP3_DB2,
        #                                     config.DB_USER_MPP3_DB2,
        #                                     config.DB_PASSWORD_MPP3_DB2)
        # XLogger.getInstance().logger.info('connect db_conn_mpp3 success')
    except Exception as e:
        # db_conn_sts = None
        db_conn_mpp = None

        XLogger.getInstance().logger.critical(str(e))
    # if db_conn_sts is None:
    #     return
    if db_conn_mpp is None:
        return
    start = datetime.datetime.now()

    p_day_1 = (start - datetime.timedelta(days=1)).strftime("%Y%m%d")
    p_day_2 = (start - datetime.timedelta(days=61)).strftime("%Y%m%d")
    start_time = p_day_2 + '200000'
    end_time = p_day_1 + '200000'
    #设定起始时间结束时间
    start_time = '20220601200000'
    end_time = '20220801200000'

    #此SQL是热轧的SQL，可能需修改
    # sql = " SELECT MAT_NO, " \
    #       " MAT_SEQ_NO, " \
    #       " MAT_TRACK_NO, " \
    #       " FACTORY_DIV, " \
    #       " PRE_UNIT_CODE, " \
    #       " NEXT_UNIT_CODE, " \
    #       " MAT_STATUS, " \
    #       " DUMMY_COIL_FLAG, " \
    #       " MAT_ACT_THICK, " \
    #       " MAT_ACT_WIDTH, " \
    #       " MAT_ACT_LEN, " \
    #       " MAT_WT, " \
    #       " ST_NO, " \
    #       " PROD_END_TIME, " \
    #       " SIGN_CODE, " \
    #       " ORDER_NO, " \
    #       " OLD_ORDER_NO, " \
    #       " BACKLOG, " \
    #       " TRANSFER_FLAG, " \
    #       " HOLD_CAUSE_CODE, " \
    #       " HOLD_TIME, " \
    #       " HOLD_COUNT, " \
    #       " PLAN_SEND_TIME, " \
    #       " CASE WHEN LENGTH(PLAN_SEND_TIME)=14 then '' else PLAN_SEND_TIME  END as ORDER_NO_CC, " \
    #       " MNG_HOLD_FLAG,REC_CREATE_TIME,REC_REVISE_TIME " \
    #       " FROM " \
    #       " MMHR.HMMHR01  " \
    #       " where PROD_END_TIME >='%s'  " \
    #       " and DUMMY_COIL_FLAG !='1'  " \
    #       " and TRANSFER_FLAG !='D'  " \
    #       " and PROD_END_TIME < '%s'  " %(start_time,end_time)
    # self.logger.info(sql)
    #此SQL是目前热镀锌所用的SQL后续可以替换，由于当时只为了导出文件，为了后续程序顺利运行，需要补上部分字段，或者直接使用下面注释掉的SQL
    sql = " select    t1.MAT_SEQ_NO,  t1.FACTORY_DIV,  t1.MAT_ACT_THICK,  t1.MAT_ACT_WIDTH,  t1.MAT_ACT_LEN, " \
          " t1.MAT_WT,  t1.ST_NO,  t1.PRE_UNIT_CODE,  t1.NEXT_UNIT_CODE,    t1.TRANSFER_FLAG,  t1.MNG_HOLD_FLAG,  t1.TECH_HOLD_FLAG,  t1.DUMMY_COIL_FLAG, t1.BACKLOG as BACKLOG,  t1.SG_SIGN, " \
          " t1.HOLD_FLAG,t1.SIGN_CODE__1_2,t1.SIGN_CODE__3_4,t1.SIGN_CODE__5_6,t1.SIGN_CODE__7_8,t1.SIGN_CODE__9_9,t1.SIGN_CODE__10_11, " \
          " t3.PLATE_TYPE_CODE,t3.NEXT_PROC_CODE,t3.TOP_PLATE_WT_AIM,t3.BOT_PLATE_WT_AIM " \
          " from ( " \
          " SELECT MAT_NO,   MAT_TRACK_NO,  MAT_SEQ_NO,  FACTORY_DIV,  MAT_ACT_THICK,  MAT_ACT_WIDTH,  MAT_ACT_LEN,   " \
          " MAT_WT,  ST_NO,  PRE_UNIT_CODE,  NEXT_UNIT_CODE, PROD_END_TIME, TRANSFER_FLAG,  MNG_HOLD_FLAG,  TECH_HOLD_FLAG,  DUMMY_COIL_FLAG,   BACKLOG,  SG_SIGN,     " \
          " left(SIGN_CODE,2) as SIGN_CODE__1_2,right(left(SIGN_CODE,4),2) as SIGN_CODE__3_4,right(left(SIGN_CODE,6),2) as SIGN_CODE__5_6,right(left(SIGN_CODE,8),2) as SIGN_CODE__7_8,right(left(SIGN_CODE,9),1) as SIGN_CODE__9_9,right(left(SIGN_CODE,11),2) as SIGN_CODE__10_11,   " \
          " CASE when HOLD_COUNT>=1 then 1 else 0 end as HOLD_FLAG,PSR_AC as ORDER_NO_CC " \
          " FROM  MMCR.HMMCR01   where PROD_END_TIME >='%s' " \
          " and DUMMY_COIL_FLAG !='1'   and TRANSFER_FLAG !='D'   and PROD_END_TIME < '%s'   " \
          " and PRE_UNIT_CODE in ('C108','C208','C408','C308','C508','C608','C708','C008','C122','C808','CA08') " \
          " ) as t1 " \
          " left join  " \
          " BGTAMOQMTO.T_ODS_TQMTO29 as t3 " \
          " on t1.ORDER_NO_CC=t3.ORDER_NO and t1.BACKLOG=t3.BACKLOG "  % (start_time, end_time)
    # sql = " select t1.*,t2.OUT_MAT_THICK,t2.OUT_MAT_WIDTH,t3.PLATE_TYPE_CODE,t3.NEXT_PROC_CODE,t3.TOP_PLATE_WT_AIM,t3.BOT_PLATE_WT_AIM, t4.SG_SIGN as SG_SIGN_ORDER,t4.ST_NO as ST_NO_ORDER,t4.SIGN_CODE as SIGN_CODE_ORDER " \
    #       " from ( " \
    #       " SELECT MAT_NO,  MAT_TRACK_NO,  MAT_STATUS,  MAT_SEQ_NO,  FACTORY_DIV,  MAT_ACT_THICK,  MAT_ACT_WIDTH,  MAT_ACT_LEN, " \
    #       " MAT_WT,  ST_NO,  PRE_UNIT_CODE,  NEXT_UNIT_CODE,  PROD_END_TIME,  TRANSFER_FLAG,  HOLD_COUNT,  HOLD_CAUSE_CODE, " \
    #       " HOLD_TIME,  MNG_HOLD_FLAG,  TECH_HOLD_FLAG,  DUMMY_COIL_FLAG,  ORDER_NO,  BACKLOG,  SIGN_CODE,  SG_SIGN, " \
    #       " PSR_AC as ORDER_NO_CC,  REC_CREATE_TIME,REC_REVISE_TIME   " \
    #       " FROM  MMCR.HMMCR01   where PROD_END_TIME >='%s'     " \
    #       " and DUMMY_COIL_FLAG !='1'   and TRANSFER_FLAG !='D'   and PROD_END_TIME < '%s'    " \
    #       " and PRE_UNIT_CODE in ('C108','C208','C408','C308','C508','C608','C708','C008','C122','C808','CA08') " \
    #       " ) as t1 " \
    #       " left join  " \
    #       " BGTAMOQMTO.T_ODS_TQMTON1 as t2 " \
    #       " on t1.ORDER_NO_CC=t2.ORDER_NO and t1.BACKLOG=t2.BACKLOG and t1.PRE_UNIT_CODE=t2.UNIT_CODE " \
    #       " left join  " \
    #       " BGTAMOQMTO.T_ODS_TQMTO29 as t3 " \
    #       " on t1.ORDER_NO_CC=t3.ORDER_NO and t1.BACKLOG=t3.BACKLOG " \
    #       " left join  " \
    #       " M1_WE.SU_WE00_MAHT01 as t4 " \
    #       " on t1.ORDER_NO_CC=t4.ORDER_NO  " % (start_time, end_time)
    df_1 = XRetryableQuery(p_db_conn=db_conn_mpp, p_sql=sql, p_max_times=5).redo()
    success = df_1.empty is False
    if success is False:
        return
    df_1.columns = df_1.columns.str.upper()
    #在目录下生成xlxs文件
    xlsx_name = 'df_yuanshi_0819.xlsx'
    writer = pd.ExcelWriter(xlsx_name)
    df_1.to_excel(writer, sheet_name='sheet1')
    writer.save()
    #在目录下生成csv文件
    # outputpath = 'fenxi.csv'
    # df_1.to_csv(outputpath,sep=',', index=False, header=True)



    #当df读取REC_REVISE_TIME，REC_CREATE_TIME后，对时间进行减法，得到封锁材料生产周期：材料历史表中取记录创建时间和记录归档时间差值作为材料的周期(REC_ERASE_TIME-REC_REVISE_TIME)到天，小数点1位
    def __cal_gap(x):
        # NOTE 绝绝绝绝绝绝绝绝绝绝绝绝绝对不是简单的相减， 这是2个时间差，得先转换为时间再计算差
        if len(x.REC_REVISE_TIME) == 14 and len(x.REC_CREATE_TIME) == 14:
            REC_REVISE_TIME = datetime.datetime.strptime(str(x.REC_REVISE_TIME), "%Y%m%d%H%M%S")
            REC_CREATE_TIME = datetime.datetime.strptime(str(x.REC_CREATE_TIME), "%Y%m%d%H%M%S")

            rst = (REC_REVISE_TIME - REC_CREATE_TIME)
            rst = round(rst.days + (rst.seconds / 60 / 60 / 24), 1)
        else:
            rst = -1

        return rst
    df_1['GAP'] = df_1.apply(lambda x: __cal_gap(x), axis=1)
    #如果sql中已经进行判定 此函数注释掉
    def __cal_IF_HOLD(x):

        if x.HOLD_COUNT >= 1:
            rst = 1
        else:

            rst = 0
        return rst

    df_1['HOLD_FLAG'] = df_1.apply(lambda x: __cal_IF_HOLD(x), axis=1)
    # df_1.drop(['GAP'], axis=1, inplace=True)
    # df_1.drop(['HOLD_COUNT'], axis=1, inplace=True)
    # df_1.drop(['HOLD_CAUSE_CODE'], axis=1, inplace=True)
    # df_1.drop(['HOLD_TIME'], axis=1, inplace=True)
    # df_1.drop(['REC_CREATE_TIME'], axis=1, inplace=True)
    # df_1.drop(['REC_REVISE_TIME'], axis=1, inplace=True)
    # df_1.drop(['OUT_MAT_THICK'], axis=1, inplace=True)
    # df_1.drop(['OUT_MAT_WIDTH'], axis=1, inplace=True)
    # df_1.drop(['SG_SIGN_ORDER'], axis=1, inplace=True)
    # df_1.drop(['ST_NO_ORDER'], axis=1, inplace=True)
    # df_1.drop(['SIGN_CODE_ORDER'], axis=1, inplace=True)
    # df_1.drop(['ORDER_NO'], axis=1, inplace=True)
    # df_1.drop(['ORDER_NO_CC'], axis=1, inplace=True)
    # df_1.drop(['MAT_STATUS'], axis=1, inplace=True)

    #简单分组计算每组的封锁率，v是group by的内容。
    df_1['INDEX'] = 1
    # v = ['ORDER_NO_CC',
    #      'BACKLOG',
    #      'PRE_UNIT_CODE']
    v = ['BACKLOG']
    df_21 = df_1.groupby(v)['MAT_WT'].agg([np.sum]).round(2)
    df_21.rename(columns={'sum': 'SUM_WT'}, inplace=True)
    df_31 = df_1.groupby(v)['INDEX'].agg([np.sum]).round(2)
    df_31.rename(columns={'sum': 'SUM_NUM'}, inplace=True)
    df_231 = pd.merge(df_21, df_31, on=v, how='left')
    # df_001 = df_1[['ORDER_NO_CC','BACKLOG','PRE_UNIT_CODE']]
    df_001 = df_1[v]

    df_001.drop_duplicates(subset=v, keep='first', inplace=True)
    df_12 = pd.merge(df_001, df_231, on=v, how='left')
    df_01 = df_1[df_1['HOLD_COUNT'] >= 1]
    df_01['INDEX'] = 1
    # v = ['ORDER_NO_CC',
    #      'BACKLOG',
    #      'PRE_UNIT_CODE']
    df_021 = df_01.groupby(v)['MAT_WT'].agg([np.sum]).round(2)
    df_021.rename(columns={'sum': 'HOLD_WT'}, inplace=True)
    df_031 = df_01.groupby(v)['INDEX'].agg([np.sum]).round(2)
    df_031.rename(columns={'sum': 'HOLD_NUM'}, inplace=True)
    df_0231 = pd.merge(df_021, df_031, on=v, how='left')
    # df_0001 = df_01[['ORDER_NO_CC','BACKLOG','PRE_UNIT_CODE']]
    df_0001 = df_01[v]

    df_0001.drop_duplicates(subset=v, keep='first', inplace=True)
    df_012 = pd.merge(df_0001, df_0231, on=v, how='left')
    df_112 = pd.merge(df_12, df_012, on=v, how='left')
    df_112['HOLD_RATE_WT'] = df_112['HOLD_WT'] / df_112['SUM_WT']
    df_112['HOLD_RATE_NUM'] = df_112['HOLD_NUM'] / df_112['SUM_NUM']
    #可以将注释打开得到分组后计算的两种封锁率结果
    # xlsx_name = 'df_B.xlsx'
    # writer = pd.ExcelWriter(xlsx_name)
    # df_112.to_excel(writer, sheet_name='sheet1')
    # writer.save()
    #此处的w也是分组的，只不过是求平均工序停留周期的，可以修改但需要带上HOLD_FALG以便分别计算有封锁材料及无封锁材料的平均工序停留周期
    w = ['BACKLOG','HOLD_FLAG']
    df_91 = df_1.groupby(w)['GAP'].agg([np.sum]).round(2)
    df_91.rename(columns={'sum': 'SUM_GAP'}, inplace=True)
    df_81 = df_1.groupby(w)['INDEX'].agg([np.sum]).round(2)
    df_81.rename(columns={'sum': 'SUM_INDEX'}, inplace=True)
    df_891 = pd.merge(df_81, df_91, on=w, how='left')
    df_891['AVG_GAP'] = df_891['SUM_GAP'] / df_891['SUM_INDEX']
    df_1001 = df_1[w]
    df_1001.drop_duplicates(subset=w, keep='first', inplace=True)
    df_10890 = pd.merge(df_1001, df_891, on=w, how='left')
    #将分组计算有封锁材料及无封锁材料的平均工序停留周期的结果输出成xlsx
    # xlsx_name = 'df_AVG_GAP3.xlsx'
    # writer = pd.ExcelWriter(xlsx_name)
    # df_10890.to_excel(writer, sheet_name='sheet1')
    # writer.save()
    print(df_891)

    # df = XDataFrameUtils.excel2dataframe(p_excel_file_path='holdtest.xlsx')


    print('success')

    try:
        # util.closeConnection(db_conn_sts)
        util.closeConnection(db_conn_mpp)
        # util.closeConnection(db_conn_mpp3)
    except Exception as e:
        XLogger.getInstance().logger.error(str(e))
    print('每天03：30分执行该定时任务')

    pass


if __name__ == '__main__':
    start = datetime.datetime.now()

    status = main()
    elapsed = float((datetime.datetime.now() - start).seconds)
    print("Time Used 4 All ----->>>> %f seconds" % (elapsed))
